package com.darren.euc.dao.impl;


import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.stereotype.Repository;

import com.darren.euc.dao.RoomDao;
import com.darren.euc.entity.Room;


@Repository
public class RoomDaoImpl extends BaseDaoImpl<Room, Integer> implements RoomDao {
	
	@SuppressWarnings("unchecked")
	@Override
	public List<Room> getAll() {
		Session session = sessionFactory.getCurrentSession();	
		return session.createQuery("from Room where enable=true").list();
	}
	
	@SuppressWarnings("unchecked")
	public List<Room> getRoomByClientAndSpaceId(Integer clientId,Integer spaceId){		
		Session session = sessionFactory.getCurrentSession();	
		String SQL = "SELECT * from euc_room  where id in (SELECT distinct product.room_id from euc_orders orders LEFT JOIN euc_contracts contract on contract.order_id = orders.id  LEFT JOIN euc_contract_product product on product.contract_id = contract.id  where orders.client_id = ?  and orders.space_id = ?  and product.id is not null);";
		Query query = session.createSQLQuery(SQL).addEntity(Room.class);
		query.setInteger(0, clientId);
		query.setInteger(1, spaceId);
		return query.list();
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<Room> findRooByMemberId(Integer memberId){		
		Session session = sessionFactory.getCurrentSession();	
		String SQL = " SELECT room.* from euc_room room "+
					" INNER JOIN euc_contract_product product on product.room_id = room.id and CURRENT_DATE() BETWEEN product.rent_start and product.rent_end  " +
					" INNER JOIN euc_contracts contract on product.contract_id = contract.id   " +
					" INNER JOIN euc_orders orders on orders.id = contract.order_id  " +
					" INNER JOIN euc_client client on orders.client_id = client.id  " +
					" INNER JOIN euc_member member on member.client_id = client.id  " +
					" WHERE member.id = ?  " +
					" and find_in_set(cast(room.id as char), member.room_access) " +
					" ORDER BY room.id ASC ";
		Query query = session.createSQLQuery(SQL).addEntity(Room.class);
		query.setInteger(0, memberId);
		return query.list();
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<Room> findRooByMemberIdWithoutTimeLimit(Integer memberId){
		Session session = sessionFactory.getCurrentSession();	
		String SQL = " SELECT room.* from euc_room room "+
					" INNER JOIN euc_contract_product product on product.room_id = room.id  " +
					" INNER JOIN euc_contracts contract on product.contract_id = contract.id   " +
					" INNER JOIN euc_orders orders on orders.id = contract.order_id  " +
					" INNER JOIN euc_client client on orders.client_id = client.id  " +
					" INNER JOIN euc_member member on member.client_id = client.id  " +
					" WHERE member.id = ?  " +
					" and find_in_set(cast(room.id as char), member.room_access) " +
					" ORDER BY room.id ASC ";
		Query query = session.createSQLQuery(SQL).addEntity(Room.class);
		query.setInteger(0, memberId);
		return query.list();
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<Room> findRoomBySpaceId(Integer spaceId){
		Session session = sessionFactory.getCurrentSession();	
		String SQL = " SELECT * from euc_room room where space_id = ? order by convert(room_name using gbk) asc; ";
		Query query = session.createSQLQuery(SQL).addEntity(Room.class);
		query.setInteger(0, spaceId);
		return query.list();
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<Room> queryAvaliableRoom(Integer spaceId,String roomName,String rentStart,String rentEnd){		
		Session session = sessionFactory.getCurrentSession();	
		String SQL = "SELECT *  FROM	euc_room room WHERE space_id = ? and enable=1 and room_name = ?  and NOT EXISTS (SELECT 1 FROM euc_contract_product product WHERE	product.room_id = room.id  AND ( ?  BETWEEN product.rent_start AND product.rent_end	OR ? BETWEEN product.rent_start AND product.rent_end OR product.rent_start BETWEEN ? AND ?  OR product.rent_end BETWEEN ?  AND ?));";
		SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd"); 
		Query query = session.createSQLQuery(SQL).addEntity(Room.class);
		query.setInteger(0, spaceId);
		query.setString(1,roomName);
		try {
			query.setDate(2,format.parse(rentStart));
			query.setDate(3,format.parse(rentEnd));
			query.setDate(4,format.parse(rentStart));
			query.setDate(5,format.parse(rentEnd));
			query.setDate(6,format.parse(rentStart));
			query.setDate(7,format.parse(rentEnd));
		} catch (ParseException e) {
			System.out.println(e.getMessage());
		}		
		return query.list();
	}
	
	
}